#include "studydbmanager.h"
#include "../share/global.h"
#include "../share/studyrecord.h"
#include "../MainStation/mainwindow.h"
#include "logdbmanager.h"

#include <QSqlDatabase>
#include <QSqlQuery>
#include <QVariant>
#include <QFile>
#include <QDir>
#include <QSqlRecord>
#include <QDebug>

QString StudyDbManager::lastError;

bool StudyDbManager::createStudyDb(bool recreate)
{
    QString imageDbPath = mainWindow->getDbConfig().imagePath;
    if (!QDir().mkpath(imageDbPath)) return false;

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", STUDY_DB_CONNECTION_NAME);
    db.setDatabaseName(QString("%1/%2").arg(imageDbPath, STUDY_DB_NAME));
    if (db.open()) {
        if (recreate) {
            db.exec("DROP INDEX IX_StudyTable_StudyDate ON StudyTable");
            db.exec("DROP TABLE StudyTable");
            db.exec("DROP INDEX IX_ImageTable_ImageTime ON ImageTable");
            db.exec("DROP TABLE ImageTable");
            db.exec("DROP INDEX IX_ReportTable_CreateTime ON ReportTable");
            db.exec("DROP TABLE ReportTable");
        }
        db.exec("CREATE TABLE IF NOT EXISTS StudyTable(StudyUid VARCHAR(128) PRIMARY KEY NOT NULL,"
                "AccNumber VARCHAR(64) NOT NULL, PatientId VARCHAR(64) NOT NULL,"
                "PatientName VARCHAR(64), PatientSex VARCHAR(2) NOT NULL,"
                "PatientBirth DATE NOT NULL, PatientAge VARCHAR(6),"
                "AdmissionId TEXT, StudyTime DATETIME NOT NULL,"
                "Modality VARCHAR(2) NOT NULL, IsAcquisited VARCHAR(6), IsReported VARCHAR(6),"
                "IsPrinted VARCHAR(6), IsSent VARCHAR(6), StudyDesc TEXT,"
                "ReqPhysician VARCHAR(64), PerPhysician VARCHAR(64),"
                "MedicalAlerts TEXT, PatientSize VARCHAR(6), PatientWeight VARCHAR(6),"
                "PatientAddr TEXT, PatientPhone VARCHAR(16))");
        db.exec("CREATE INDEX IF NOT EXISTS IX_StudyTable_StudyDate ON StudyTable(StudyTime)");
        db.exec("CREATE TABLE IF NOT EXISTS ImageTable(ImageUid VARCHAR(128) PRIMARY KEY NOT NULL,"
                "SopClassUid VARCHAR(128) NOT NULL,"
                "SeriesUid VARCHAR(128) NOT NULL, StudyUid VARCHAR(128) NOT NULL,"
                "RefImageUid VARCHAR(128),"
                "ImageNo VARCHAR(16), ImageTime DATETIME NOT NULL,"
                "BodyPart VARCHAR(128), IsPrinted VARCHAR(6), IsSent VARCHAR(6),"
                "ImageDesc TEXT, ImageFile TEXT,"
                "FOREIGN KEY(StudyUid) REFERENCES StudyTable(StudyUid))");
        db.exec("CREATE INDEX IF NOT EXISTS IX_ImageTable_ImageTime ON ImageTable(ImageTime)");
        db.exec("CREATE TABLE IF NOT EXISTS ReportTable(ReportUid VARCHAR(128) PRIMARY KEY NOT NULL,"
                "SeriesUid VARCHAR(128) NOT NULL, StudyUid VARCHAR(128) NOT NULL,"
                "PatientName VARCHAR(64), PatientSex VARCHAR(2), ReqPhysician VARCHAR(64),"
                "ReqDivision VARCHAR(64), StudyExpenses VARCHAR(64),"
                "CreateTime DATETIME NOT NULL, ContentTime DATETIME NOT NULL,"
                "IsCompleted VARCHAR(6), IsVerified VARCHAR(6), IsPrinted VARCHAR(6),"
                "ReportFile TEXT,"
                "FOREIGN KEY(StudyUid) REFERENCES StudyTable(StudyUid))");
        db.exec("CREATE INDEX IF NOT EXISTS IX_ReportTable_CreateTime ON ReportTable(CreateTime)");
    }

    lastError = db.lastError().text();
    return db.lastError().type()==QSqlError::NoError;
}

bool StudyDbManager::insertStudyToDb(const StudyRecord &study, bool imported)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare(QString("INSERT INTO StudyTable VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"));
    query.addBindValue(study.studyUid);
    query.addBindValue(study.accNumber);
    query.addBindValue(study.patientId);
    query.addBindValue(study.patientName);
    query.addBindValue(study.patientSex);
    query.addBindValue(study.patientBirth.toString("yyyy-MM-dd"));
    query.addBindValue(study.patientAge);
    query.addBindValue(study.admissionId);
    query.addBindValue(study.studyTime.toString(SQLITE_DATETIME_FORMAT));
    query.addBindValue(study.modality);

    query.addBindValue(QObject::tr("No"));
    query.addBindValue(QObject::tr("No"));
    query.addBindValue(QObject::tr("No"));
    query.addBindValue(QObject::tr("No"));

    query.addBindValue(study.studyDesc);
    query.addBindValue(study.reqPhysician);
    query.addBindValue(study.perPhysician);

    query.addBindValue(study.medicalAlerts);
    query.addBindValue(study.patientSize);
    query.addBindValue(study.patientWeight);
    query.addBindValue(study.patientAddr);
    query.addBindValue(study.patientPhone);
    bool ok = query.exec();


    QString msg = QObject::tr("Acc Num: %1, Patient Id: %2, Patient Name: %3, Study Uid: %4")
            .arg(study.accNumber, study.patientId, study.patientName, study.studyUid);
    if (ok) {
        msg = QObject::tr("Study inserted, %1.").arg(msg);
        LogDbManager::insertMessageToDb(imported?LogDbManager::ET_ImportStudy:LogDbManager::ET_InserStudy, msg);
    } else {
        msg = QObject::tr("Insert study failed, %1, Reason: %2.").arg(msg, query.lastError().text());
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::removeStudyFromDb(const QString &studyUid)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("SELECT AccNumber, PatientId, PatientName FROM StudyTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    bool ok = query.exec();
    if (ok && query.first()) {
        QSqlRecord rec = query.record();
        query.prepare("DELETE FROM StudyTable WHERE StudyUid=?");
        query.addBindValue(studyUid);
        ok = query.exec();

        QString msg = QObject::tr("Acc Num: %1, Patient Id: %2, Patient Name: %3, Study Uid: %4")
                .arg(rec.value(0).toString(), rec.value(1).toString(), rec.value(2).toString(), studyUid);
        if (ok) {
            msg = QObject::tr("Study removed, %1.").arg(msg);
            LogDbManager::insertMessageToDb(LogDbManager::ET_RemoveStudy, msg);
            removeAllImagesOfStudyFromDb(studyUid, false);
            removeAllReportsOfStudyFromDb(studyUid, false);
        } else {
            msg = QObject::tr("Remove study failed, %1, Reason: %2.").arg(msg, query.lastError().text());
            LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
        }
    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateStudyAcquisitStatus(const QString &studyUid)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    bool ok = true;

    query.prepare("SELECT ImageUid FROM ImageTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    ok = query.exec();
    if (ok) {
        QString isAcq = QObject::tr("No");
        if (query.next()) isAcq = QObject::tr("Yes");
        query.prepare("UPDATE StudyTable SET IsAcquisited=? WHERE StudyUid=?");
        query.addBindValue(isAcq);
        query.addBindValue(studyUid);
        ok = query.exec();

        QString msg = QObject::tr("Study Uid: %1, Is Acquisited: %2").arg(studyUid, isAcq);
        if (ok) {
           msg = QObject::tr("Study updated, %1.").arg(msg);
           LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateStudy, msg);
        } else {
            msg = QObject::tr("Update study failed, %1, Reason: %2.").arg(msg, query.lastError().text());
            LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
        }


    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateStudyPrintStatus(const QString &studyUid)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("SELECT IsPrinted FROM ImageTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    bool ok = query.exec();
    if (ok) {
        bool allPrinted = true;
        bool nonePrinted = true;
        while (query.next()) {
            QString status = query.record().value(0).toString();
            if ((status == QObject::tr("Yes")) || (status == "Yes")) {
                nonePrinted = false;
            } else {
                allPrinted = false;
            }
        }
        QString printed = nonePrinted?QObject::tr("No"):(allPrinted?QObject::tr("Yes"):QObject::tr("Partial"));

        query.prepare("UPDATE StudyTable SET IsPrinted=? WHERE StudyUid=?");
        query.addBindValue(printed);
        query.addBindValue(studyUid);
        ok = query.exec();
        QString msg = QObject::tr("Study Uid: %1, IsPrinted: %2").arg(studyUid, printed);
        if (ok) {
            msg = QObject::tr("Study updated, %1.").arg(msg);
            LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateStudy, msg);
        } else {
            msg = QObject::tr("Failed to update study, %1, Reason: %2.").arg(msg, query.lastError().text());
            LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
        }
    }

    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateStudyReportStatus(const QString &studyUid)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("SELECT ReportUid FROM ReportTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    bool ok = query.exec();
    if (ok) {
        QString isRep = QObject::tr("No");
        if (query.next()) isRep = QObject::tr("Yes");
        query.prepare("UPDATE StudyTable SET IsReported=? WHERE StudyUid=?");
        query.addBindValue(isRep);
        query.addBindValue(studyUid);
        ok = query.exec();
        QString msg = QObject::tr("Study Uid: %1, Is Reported: %2").arg(studyUid, isRep);
        if (ok) {
            msg = QObject::tr("Study updated, %1.").arg(msg);
            LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateStudy, msg);
        } else {
            msg = QObject::tr("Failed to update study, %1, Reason: %2.").arg(msg, query.lastError().text());
            LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
        }
    }

    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateStudySendStatus(const QString &studyUid)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("SELECT IsSent FROM ImageTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    bool ok = query.exec();
    if (ok) {
        bool allSent = true;
        bool noneSent = true;
        while (query.next()) {
            QString status = query.record().value(0).toString();
            if ((status == QObject::tr("Yes")) || (status == "Yes")) {
                noneSent = false;
            } else {
                allSent = false;
            }
        }
        QString sentStr = noneSent?QObject::tr("No"):(allSent?QObject::tr("Yes"):QObject::tr("Partial"));
        query.prepare("UPDATE StudyTable SET IsSent=? WHERE StudyUid=?");
        query.addBindValue(sentStr);
        query.addBindValue(studyUid);
        ok = query.exec();
        QString msg = QObject::tr("Study Uid: %1, Is Sent: %2").arg(studyUid, sentStr);
        if (ok) {
            msg = QObject::tr("Study updated, %1.").arg(msg);
            LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateStudy, msg);
        } else {
            msg = QObject::tr("Failed to update study, %1, Reason: %2.").arg(msg, query.lastError().text());
            LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
        }
    }

    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::insertImageToDb(const ImageRecord &image, bool imported)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare(QString("INSERT INTO ImageTable VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"));
    query.addBindValue(image.imageUid);
    query.addBindValue(image.sopClassUid);
    query.addBindValue(image.seriesUid);
    query.addBindValue(image.studyUid);
    query.addBindValue(image.refImageUid);
    query.addBindValue(image.imageNo);
    query.addBindValue(image.imageTime.toString(SQLITE_DATETIME_FORMAT));
    query.addBindValue(image.bodyPart);
    query.addBindValue(QObject::tr("No"));
    query.addBindValue(QObject::tr("No"));
    query.addBindValue(image.imageDesc);
    query.addBindValue(image.imageFile);

    bool ok = query.exec();
    QString msg = QObject::tr("Image Uid: %1, Study Uid: %2, Image File: %3")
            .arg(image.imageUid, image.studyUid, image.imageFile);
    if (ok) {
        msg = QObject::tr("Image inserted, %1.").arg(msg);
        LogDbManager::insertMessageToDb(imported?LogDbManager::ET_ImportImage:LogDbManager::ET_InsertImage, msg);
        updateStudyAcquisitStatus(image.studyUid);
    } else {
        msg = QObject::tr("Insert image failed, %1, Reason: %2.").arg(msg, query.lastError().text());
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::removeImageFromDb(const QString &imageUid, bool updateStudy)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("SELECT RefImageUid, StudyUid, ImageFile FROM ImageTable WHERE ImageUid=?");
    query.addBindValue(imageUid);
    bool ok = query.exec();
    if (ok && query.first()) {
        QSqlRecord rec = query.record();
        QString refUid = rec.value(0).toString();
        QString file = QString("%1/%2").arg(mainWindow->getDbConfig().imagePath, rec.value(2).toString());
        QFile(file).remove();
        QString dirName = file.left(file.lastIndexOf('/'));
        if (!refUid.isEmpty()){
            QString rawFile = QString("%1/%2_%3.dcm").arg(dirName, QString(RAW_IMAGE_PREFIX), refUid);
            QFile(rawFile).remove();
        }
        QDir().rmpath(dirName);

        query.prepare("DELETE FROM ImageTable WHERE ImageUid=?");
        query.addBindValue(imageUid);
        bool ok = query.exec();

        QString msg = QObject::tr("Image Uid: %1, Study Uid: %2, Image File: %3")
                .arg(imageUid, rec.value(1).toString(), rec.value(2).toString());
        if (ok) {
            msg = QObject::tr("Image removed, %1.").arg(msg);
            LogDbManager::insertMessageToDb(LogDbManager::ET_RemoveImage, msg);
            if (updateStudy) {
                updateStudyAcquisitStatus(rec.value(1).toString());
                updateStudyPrintStatus(rec.value(1).toString());
                updateStudySendStatus(rec.value(1).toString());
            }
        } else {
            msg = QObject::tr("Remove image failed, %1, Reason: %2.").arg(msg, query.lastError().text());
            LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
        }
    }
    lastError = query.lastError().text();
    qDebug() << query.lastError();
    return ok;
}

bool StudyDbManager::removeAllImagesOfStudyFromDb(const QString &studyUid, bool updateStudy)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("SELECT ImageUid FROM ImageTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    bool ok = query.exec();
    if (ok) {
        while (query.next())
            removeImageFromDb(query.record().value(0).toString(), false);
        if (updateStudy) {
            updateStudyAcquisitStatus(studyUid);
            updateStudyPrintStatus(studyUid);
            updateStudySendStatus(studyUid);
        }
    }
    /*
    query.prepare("SELECT RefImageUid, ImageFile FROM ImageTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    if (query.exec()) {
        while (query.next()) {
            QSqlRecord rec = query.record();
            QString refUid = rec.value(0).toString();
            QString file = QString("%1/%2").arg(mainWindow->getDbLocation(), rec.value(1).toString());
            QFile(file).remove();
            QString dirName = file.left(file.lastIndexOf('/'));
            if (!refUid.isEmpty()){
                QString rawFile = QString("%1/%2_%3.dcm").arg(dirName, QString(RAW_IMAGE_PREFIX), refUid);
                QFile(rawFile).remove();
            }
            QDir().rmpath(dirName);
        }
    }
    query.prepare("DELETE FROM ImageTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    bool ok = query.exec();
    lastError = query.lastError().text();

    if (ok) {
        QString msg = QObject::tr("Removed all images of study \"%1\".").arg(studyUid);
        LogDbManager::insertMessageToDb(LogDbManager::ET_RemoveImage, msg);
        updateStudyAcquisitStatus(studyUid);
    } else {
        QString msg = QObject::tr("Failed to remove images of study \"%1\", Reason: %2.").arg(studyUid, lastError);
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
*/
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::insertReportToDb(const ReportRecord &report, bool imported)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare(QString("INSERT INTO ReportTable VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"));
    query.addBindValue(report.reportUid);
    query.addBindValue(report.seriesUid);
    query.addBindValue(report.studyUid);
    query.addBindValue(report.patientName);
    query.addBindValue(report.patientSex);
    query.addBindValue(report.reqPhysician);
    query.addBindValue(report.reqDivision);
    query.addBindValue(report.expenses);
    query.addBindValue(report.createTime.toString(SQLITE_DATETIME_FORMAT));
    query.addBindValue(report.contentTime.toString(SQLITE_DATETIME_FORMAT));
    query.addBindValue(report.isCompleted?QObject::tr("Yes"):QObject::tr("No"));
    query.addBindValue(report.isVerified?QObject::tr("Yes"):QObject::tr("No"));
    query.addBindValue(QObject::tr("No"));
    query.addBindValue(report.reportFile);
    bool ok = query.exec();
    QString msg = QObject::tr("Report Uid: %1, Study Uid: %2, Report File: %3")
            .arg(report.reportUid, report.studyUid, report.reportFile);
    if (ok) {
        msg = QObject::tr("Report inserted, %1.").arg(msg);
        LogDbManager::insertMessageToDb(imported?LogDbManager::ET_ImportReport:LogDbManager::ET_CreateReport, msg);
        updateStudyReportStatus(report.studyUid);
    } else {
        msg = QObject::tr("Insert report failed, %1, Reason: %2.").arg(msg, query.lastError().text());
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateImagePrintStatus(const QString &imageUid, bool printed)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    QString printedStr = printed?QObject::tr("Yes"):QObject::tr("No");
    query.prepare("UPDATE ImageTable SET IsPrinted=? WHERE ImageUid=?");
    query.addBindValue(printedStr);
    query.addBindValue(imageUid);
    bool ok = query.exec();

    QString msg = QObject::tr("Image Uid: %1, Is Printed: %2").arg(imageUid, printedStr);
    if (ok) {
        msg = QObject::tr("Image Updated, %1.").arg(msg);
        LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateImage, msg);

        query.prepare("SELECT StudyUid FROM ImageTable WHERE ImageUid=?");
        query.addBindValue(imageUid);
        bool ok = query.exec();
        if (ok && query.next())
            updateStudyPrintStatus(query.record().value(0).toString());
    } else {
        msg = QObject::tr("Failed to update image, %1, Reason: %2.").arg(msg, query.lastError().text());
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateImageSendStatus(const QString &imageUid, bool sent)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    QString sentStr = sent?QObject::tr("Yes"):QObject::tr("No");
    query.prepare("UPDATE ImageTable SET IsSent=? WHERE ImageUid=?");
    query.addBindValue(sentStr);
    query.addBindValue(imageUid);
    bool ok = query.exec();

    QString msg = QObject::tr("Image Uid: %1, Is Sent: %2").arg(imageUid, sentStr);
    if (ok) {
        msg = QObject::tr("Image updated, %1.").arg(msg);
        LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateImage, msg);

        query.prepare("SELECT StudyUid FROM ImageTable WHERE ImageUid=?");
        query.addBindValue(imageUid);
        ok = query.exec();
        if (ok && query.next())
            updateStudySendStatus(query.record().value(0).toString());
    } else {
        msg = QObject::tr("Failed to update image, %1, Reason: %2.").arg(msg, query.lastError().text());
    }

    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::removeReportFromDb(const QString &reportUid, bool updateStudy)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("SELECT StudyUid, ReportFile FROM ReportTable WHERE ReportUid=?");
    query.addBindValue(reportUid);
    bool ok = query.exec();
    if (ok && query.first()) {
        QSqlRecord rec = query.record();
        QString file = QString("%1/%2").arg(mainWindow->getDbConfig().imagePath, rec.value(1).toString());
        QFile(file).remove();
        QDir().rmpath(file.left(file.lastIndexOf('/')));

        query.prepare("DELETE FROM ReportTable WHERE ReportUid=?");
        query.addBindValue(reportUid);
        bool ok = query.exec();

        QString msg = QObject::tr("Report Uid: %1, Study Uid: %2, Report File: %3")
                .arg(reportUid, rec.value(0).toString(), rec.value(1).toString());
        if (ok) {
            msg = QObject::tr("Report removed, %1.").arg(msg);
            LogDbManager::insertMessageToDb(LogDbManager::ET_RemoveReport, msg);
            if (updateStudy)
                updateStudyReportStatus(rec.value(0).toString());
        } else {
            msg = QObject::tr("Failed to remove report, %1, Reason: %2.").arg(msg, query.lastError().text());
            LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
        }
    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::removeAllReportsOfStudyFromDb(const QString &studyUid, bool updateStudy)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("SELECT ReportUid FROM ReportTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    bool ok = query.exec();
    if (ok) {
        while (query.next())
            removeReportFromDb(query.record().value(0).toString(), false);
        if (updateStudy)
            updateStudyReportStatus(studyUid);
    }
    /*
    query.prepare("SELECT ReportFile FROM ReportTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    if (query.exec()) {
        while (query.next()) {
            QSqlRecord rec = query.record();
            QString file = QString("%1/%2").arg(mainWindow->getDbLocation(), rec.value(0).toString());
            QFile(file).remove();
            QDir().rmpath(file.left(file.lastIndexOf('/')));
        }
    }

    query.prepare("DELETE FROM ReportTable WHERE StudyUid=?");
    query.addBindValue(studyUid);
    bool ok = query.exec();
    lastError = query.lastError().text();
    if (ok) {
        QString msg = QObject::tr("Removed all reports of study \"%1\".").arg(studyUid);
        LogDbManager::insertMessageToDb(LogDbManager::ET_RemoveReport, msg);
        updateStudyReportStatus(studyUid);
    } else {
        QString msg = QObject::tr("Failed to remove reports of study \"%1\", Reason: %2.")
                .arg(studyUid, lastError);
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
*/
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateImageFile(const QString &imageUid, const QString &imageFile)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("UPDATE ImageTable SET ImageFile=? WHERE ImageUid=?");
    query.addBindValue(imageFile);
    query.addBindValue(imageUid);
    bool ok = query.exec();

    QString msg = QObject::tr("Image Uid: %1, Image File: %2").arg(imageUid, imageFile);
    if (ok) {
        msg = QObject::tr("Image updated, %1.").arg(msg);
        LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateImage, msg);
    } else {
        msg = QObject::tr("Failed to update image, %1, Reason: %2.").arg(msg, query.lastError().text());
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateReportFile(const QString &reportUid, const QString &reportFile)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("UPDATE ReportTable SET ReportFile=? WHERE ReportUid=?");
    query.addBindValue(reportFile);
    query.addBindValue(reportUid);
    bool ok = query.exec();
    lastError = query.lastError().text();

    QString msg = QObject::tr("Reprot Uid: %1, Report File: %2").arg(reportUid, reportFile);
    if (ok) {
        msg = QObject::tr("Report Updated, %1.").arg(msg);
        LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateReport, msg);
    } else {
        msg = QObject::tr("Failed to update report, %1, Reason: %2.").arg(msg, query.lastError().text());
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
    return ok;
}

bool StudyDbManager::updateReportStatus(const ReportRecord &report)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    query.prepare("UPDATE ReportTable SET ReqPhysician=?, ReqDivision=?, StudyExpenses=?, ContentTime=?, IsCompleted=?, IsVerified=?, ReportFile=? WHERE ReportUid=?");
    query.addBindValue(report.reqPhysician);
    query.addBindValue(report.reqDivision);
    query.addBindValue(report.expenses);
    query.addBindValue(report.contentTime.toString(SQLITE_DATETIME_FORMAT));
    query.addBindValue(report.isCompleted?QObject::tr("Yes"):QObject::tr("No"));
    query.addBindValue(report.isVerified?QObject::tr("Yes"):QObject::tr("No"));
    query.addBindValue(report.reportFile);
    query.addBindValue(report.reportUid);
    bool ok = query.exec();

    QString msg = QObject::tr("Report Uid: %1, Content Time: %2, Report File: %3")
            .arg(report.reportUid, report.contentTime.toString(SQLITE_DATETIME_FORMAT), report.reportFile);
    if (ok) {
        msg = QObject::tr("Report updated, %1.").arg(msg);
        LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateReport, msg);
    } else {
        msg = QObject::tr("Failed to update report, %1, Reason: %2.").arg(msg, query.lastError().text());
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
    lastError = query.lastError().text();
    return ok;
}

bool StudyDbManager::updateReportPrintStatus(const QString &reportUid, bool printed)
{
    QSqlDatabase db = QSqlDatabase::database(STUDY_DB_CONNECTION_NAME);
    QSqlQuery query(db);
    QString isPrint = printed?QObject::tr("Yes"):QObject::tr("No");
    query.prepare("UPDATE ReportTable SET IsPrinted=? WHERE ReportUid=?");
    query.addBindValue(isPrint);
    query.addBindValue(reportUid);
    bool ok = query.exec();
    QString msg = QObject::tr("Report Uid: %1, Is Printed: %2").arg(reportUid, isPrint);
    if (ok) {
        msg = QObject::tr("Report updated, %1.").arg(msg);
        LogDbManager::insertMessageToDb(LogDbManager::ET_UpdateReport, msg);
    } else {
        msg = QObject::tr("Failed to update report, %1, Reason: %2.").arg(msg, query.lastError().text());
        LogDbManager::insertMessageToDb(LogDbManager::ET_SysError, msg);
    }
    lastError = query.lastError().text();
    return ok;
}
